# -*- coding: utf-8 -*-
from openpyxl import load_workbook
import pymysql
from hashlib import md5
import base64

excel_location = input('模块菜单Excel全路径（需要包含menu sheet）: ')
version = input('菜单对应版本（version_no）: ')

# 本地测试环境
# excel_location = "/Users/jialei/Documents/joysuch/副本菜单与业务模块2.9_001.xlsx"
# version = "2.8"

wb = load_workbook(excel_location)
# 显示文档中包含的 表单 名称
print(wb.sheetnames)

menu_sheet = wb.get_sheet_by_name('menu')

base_module_menu_mapping_sql_template_pre = "INSERT INTO wwyt_base_module_menu_mapping(module_name, module_code, menu_url, create_time, update_time, delete_flag) VALUES"
base_module_menu_mapping_sql_template = "('{}', '{}', '{}', now(), now(), '0')"
base_module_menu_mapping_sql_array = []

base_module_menu_mapping_md5 = ""

for i in range(2, menu_sheet.max_row + 1):
    module_name = (menu_sheet.cell(row=i,column=3)).value
    module_name_array = module_name.split("、")
    url = (menu_sheet.cell(row=i,column=4)).value
    # if len(module_name_array) > 1:
    #     print("url:{},module_name:{}".format(url, module_name))
    for mn in module_name_array:
        if mn == "基础模块":
            continue
        base_module_menu_mapping_sql_array.append(base_module_menu_mapping_sql_template.format(mn, mn, url))
        base_module_menu_mapping_md5 = base_module_menu_mapping_md5 + str(base64.b64encode(mn.encode('utf-8')), 'utf-8') + url + "0"

base_module_menu_mapping_md5 = base_module_menu_mapping_md5.encode('utf-8')
base_module_menu_mapping_md5 = md5(base_module_menu_mapping_md5).hexdigest()

# 1. 创建连接（Connection）
conn = pymysql.connect(host='172.16.5.228', port=3306,
                       user='root', password='root',
                       database='wwyt', charset='utf8mb4')

for sql in base_module_menu_mapping_sql_array:
    base_module_menu_mapping_sql_template_pre = base_module_menu_mapping_sql_template_pre + sql + ","

base_module_menu_mapping_sql_template_pre = base_module_menu_mapping_sql_template_pre[:-1]

print("1.truncate table wwyt_base_module_menu_mapping")
print("2.{}".format(base_module_menu_mapping_sql_template_pre))
print("3.update wwyt_common_version set module_md5 = '{}' where version_no  = '{}'".format(base_module_menu_mapping_md5, version))

try:
    # 2. 获取游标对象（Cursor）
    with conn.cursor() as cursor:
        # 1. drop wwyt_base_module_menu_mapping
        affected_rows = cursor.execute("truncate table wwyt_base_module_menu_mapping")
        # 2. insert wwyt_base_module_menu_mapping
        # 3. 通过游标对象向数据库服务器发出SQL语句
        affected_rows = cursor.execute(base_module_menu_mapping_sql_template_pre)
        if affected_rows == len(base_module_menu_mapping_sql_array):
            print('插入成功!!!')

        # 4. 更新
        cursor.execute("update wwyt_common_version set module_md5 = %s where version_no  = %s", (base_module_menu_mapping_md5, version))
    # 4. 提交事务（transaction）
    conn.commit()
except pymysql.MySQLError as err:
    # 4. 回滚事务
    conn.rollback()
    print(type(err), err)
finally:
    # 5. 关闭连接释放资源
    conn.close()
